package 作业;

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Properties;
import java.util.Vector;

public class opeate {
    public Driver d;
    final String url="jdbc:mysql://localhost:3306/works";
    private static Connection conn;
    private PreparedStatement gett;
    private PreparedStatement getu;
    private PreparedStatement adduser;
    private PreparedStatement addticket;
    private PreparedStatement getinfo;
    public opeate() throws SQLException {
        d=new com.mysql.cj.jdbc.Driver();
        Properties p=new Properties();
        p.setProperty("user","root");
        p.setProperty("password","111111");
        if (conn==null)
            conn=d.connect(url,p);
        gett=conn.prepareStatement("select * from tinfo where start=? and end=?");
        getu=conn.prepareStatement("select password from user where name=?");
        adduser=conn.prepareStatement("insert into user values(?,?,?)");
        addticket=conn.prepareStatement("insert into info values(?,?,?,?,?,?)");
        getinfo=conn.prepareStatement("select * from info where name=?");
    }

    public Vector<Vector<String>> GetUser_list() throws SQLException {
        Vector<String> temp=new Vector();
        Vector<Vector<String>> ans=new Vector();
        String sql="select name,password from user where type='U'";
        Statement exe=conn.createStatement();
        ResultSet resultSet=exe.executeQuery(sql);
        while(resultSet.next()){
            temp=new Vector<>();
            temp.add(resultSet.getString(1));
            temp.add(resultSet.getString(2));
            ans.add(temp);
        }
        return ans;
    }


    public Vector GetET() throws SQLException {
        String sql="select * from info";
        Statement sta=conn.createStatement();
        Vector ans=new Vector();
        ResultSet res=sta.executeQuery(sql);
        while(res.next()){
            Vector temp=new Vector();
            temp.add(res.getString(1));
            temp.add(res.getString(2));
            temp.add(res.getString(3));
            temp.add(res.getString(4));
            temp.add(res.getString(5));
            temp.add(res.getString(6));
            ans.add(temp);
        }
        return ans;
    }

    public ArrayList<Struct_ticket> finds(String start,String end) throws SQLException {
        ArrayList<Struct_ticket> ans=new ArrayList<>();
        gett.setString(1,start);
        gett.setString(2,end);
        ResultSet re=gett.executeQuery();
        while(re.next()){
            ans.add(new Struct_ticket(re.getString(1),re.getString(2),re.getFloat(3),re.getString(4),re.getInt(5)));
        }
        return ans;
    }
    public String getuser(String name,String pass) throws SQLException {
        String sql="select type from user where name=? and password=?";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,name);
        ps.setString(2,pass);
        ResultSet res=ps.executeQuery();
        if(res.next()){
            return res.getString(1);
        }
        else{
            return "";
        }
    }

    public String getuser(String name) throws SQLException {
        String ans="";
        getu.setString(1,name);
        ResultSet r=getu.executeQuery();
        if(r.next()){
            ans+=r.getString(1);
        }
        return ans;
    }

    public boolean AddUser(String username,String pass) throws SQLException {
        adduser.setString(1,username);
        adduser.setString(2,pass);
        adduser.setString(3,"U");
        try {
            adduser.executeUpdate();
        }
        catch(Exception e){
            return false;
        }
        return true;
    }

    public boolean buyticket(String start,String end,String username,double cost,String cartime) throws SQLException {
            PreparedStatement statement=conn.prepareStatement("update tinfo set quantity=quantity-1 where start=? and end=?");
            statement.setString(1,start);
            statement.setString(2,end);
            try {
                statement.executeUpdate();
                addticket(username,start,end,cost,cartime);
                return true;
            }
            catch (Exception e){
                return false;
            }
    }

    public boolean addticket(String username,String start,String end,double cost,String cartime) throws SQLException {
        DateFormat df=new SimpleDateFormat("YYYY年MM月dd日HH时mm分");
        addticket.setString(1,username);
        addticket.setString(2,start);
        addticket.setString(3,end);
        addticket.setDouble(4,cost);
        addticket.setString(5,df.format(new Date(System.currentTimeMillis())));
        addticket.setString(6,cartime);
        return 0>addticket.executeUpdate();
    }

    public ArrayList<String> GetStart() throws SQLException {
        ArrayList<String> list=new ArrayList<>();
        Statement sql=conn.createStatement();
        ResultSet res=sql.executeQuery("select start from tinfo group by start");
        while(res.next()){
            list.add(res.getString(1));
        }
        return list;
    }

    public ArrayList<String> GetEnd() throws SQLException {
        ArrayList<String> list=new ArrayList<>();
        Statement sql=conn.createStatement();
        ResultSet res=sql.executeQuery("select end from tinfo group by end");
        while(res.next()){
            list.add(res.getString(1));
        }
        return list;
    }

    public void del(String username,String time) throws SQLException {
        PreparedStatement pre=conn.prepareStatement("delete from info where name=? and cartime=?");
        pre.setString(1,username);
        pre.setString(2,time);
        pre.executeUpdate();
    }

    public String[] getinfo(String user) throws SQLException {
        ArrayList<String> ans=new ArrayList<>();
        getinfo.setString(1,user);
        ResultSet resu=getinfo.executeQuery();
        String top="发车时间                    起点站                  终点站          价格            购买时间";
        while(resu.next()){
            ans.add(resu.getString(6)+"       "+resu.getString(2)+"                "+resu.getString(3)+"          "+resu.getDouble(4)+"            "+resu.getString(5));
        }

        String [] result=new String[ans.size()+1];
        result[0]=top;
        for(int i=0;i<ans.size();i++){
            result[i+1]=ans.get(i);
        }
        return result;
    }


    public void editor(String username,String pass) throws SQLException {
        PreparedStatement pre=conn.prepareStatement("update user set password=? where name=?");
        pre.setString(1,pass);
        pre.setString(2,username);
        pre.executeUpdate();
    }
    public void close() throws SQLException {
        conn.close();
        conn=null;
    }

    public Vector GetAllTicket() throws SQLException {
        Vector ans=new Vector();
        Vector temp=new Vector();
        String sql="select * from tinfo";
        Statement statement=conn.createStatement();
        ResultSet res=statement.executeQuery(sql);
        while(res.next()){
            temp=new Vector();
            temp.add(res.getString(1));
            temp.add(res.getString(2));
            temp.add(res.getString(3));
            temp.add(res.getString(4));
            temp.add(res.getString(5));
            temp.add(res.getString(6));
            ans.add(temp);
        }
        return ans;
    }

    public void Ed_T(String start,String end,String cost,String time,Integer num,String id) throws SQLException {
        String sql="update tinfo set start=?,end=?,cost=?,time=?,quantity=? where id =?";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,start);
        ps.setString(2,end);
        ps.setDouble(3,Double.valueOf(cost));
        ps.setString(4,time);
        ps.setInt(5,num);
        ps.setInt(6,Integer.valueOf(id).intValue());
        try{
            ps.executeUpdate();
        }
        catch (Exception ee){

        }
    }

    public static void main(String[] args) throws SQLException {
        opeate p=new opeate();
        p.del("1","2024年05月16日20时31分");
//        String ans=p.getuser("于世界指代拉萨地方·");
//        System.out.println(ans.length());
//        System.out.println(ans);
////        System.out.println("够买"+p.buyticket("江西","湖北"));
//        ArrayList<Struct_ticket> a =p.finds("江西","湖北");
//        System.out.println("剩余量："+a.size());
//        for(Struct_ticket i:a)
//            System.out.println("车票"+i);

    }

}
